﻿CREATE PROCEDURE [dbo].[sp_ShowProcesses_Loacal] 
@Filter sysname = ''  
AS  
CREATE TABLE #P(
spid	smallint,
kpid	smallint,
blocked	smallint,
waittype	binary(2),
waittime	bigint,
lastwaittype	nchar(64),
waitresource	nchar(512),
dbid	smallint,
uid	smallint,
cpu	int,
physical_io	bigint,
memusage	int,
login_time	datetime,
last_batch	datetime,
ecid	smallint,
open_tran	smallint,
status	nchar(60),
sid	binary(86),
hostname	nchar(256),
program_name	nchar(256),
hostprocess	nchar(20),
cmd	nchar(32),
nt_domain	nchar(256),
nt_username	nchar(256),
net_address	nchar(24),
net_library	nchar(24),
loginame	nchar(256),
context_info	binary(128),
sql_handle	binary(20),
stmt_start	int,
stmt_end	int,
request_id	int,
EventInfo nvarchar(max)
)
CREATE TABLE #E (EventType sysname, Parameters sysname,EventInfo nvarchar(max))  
DECLARE cur CURSOR FOR select spid from master.dbo.sysprocesses where spid > 50 order by hostname  
DECLARE @ID sysname  
OPEN cur  
FETCH NEXT FROM cur INTO @ID  
 
WHILE @@FETCH_STATUS = 0 BEGIN  
INSERT INTO #P  
select *,'' from master.dbo.sysprocesses where spid = @ID  
INSERT INTO #E  
EXEC ('DBCC INPUTBUFFER('+@ID+')')  
UPDATE #P SET EventInfo = (SELECT EventInfo FROM #E) WHERE spid = @ID  
DELETE FROM #E  
FETCH NEXT FROM cur INTO @ID  
END  
 
CLOSE cur  
DEALLOCATE cur  

SELECT
spid,
blocked,
open_tran,
status,
EventInfo,
cmd,
cpu,
physical_io,
kpid,
waittype,
waittime,
lastwaittype,
waitresource,
dbid,
uid,
memusage,
login_time,
last_batch,
ecid,
sid,
hostname,
program_name,
hostprocess,
nt_domain,
nt_username,
net_address,
net_library,
loginame,
context_info,
sql_handle,
stmt_start,
stmt_end,
request_id
FROM #P WHERE ISNULL(EventInfo,'') LIKE '%'+@Filter+'%'
ORDER BY spid




